package com.yonyou.dao;

import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONArray;
import com.yonyou.model.OerYszb;
import com.yonyou.model.OerSecCzzfMlid;
import com.yonyou.model.OerSecCzzfd;
import com.yonyou.model.RequestEntity;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.Map;
import java.util.ArrayList;
import java.util.List;


/**
 * @author: wangchu
 * @date: 2025-04-29 15:19
 * @description:
 */
@Slf4j
@Repository
public class OerSecCzzfdDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询触发器结果中的待支付报销单
     */
    public List<OerSecCzzfMlid> searchNewNote() {
        return jdbcTemplate.query("SELECT * FROM OER_SEC_CZZF_MLID WHERE STATE = 0", new BeanPropertyRowMapper<>(OerSecCzzfMlid.class));
    }

    /**
     * 查询待支付信息
     */
    public List<OerSecCzzfd> searchNewCzzfd(OerSecCzzfMlid mlid) {
        return jdbcTemplate.query("SELECT a.*, b.AUDITOR FROM OER_SEC_CZZFD_VIEW a\n" +
                "LEFT JOIN OER_SEC_CZZFD_VIEW_AUDITOR b ON a.GSDM = b.GSDM AND a.KJND = b. KJND AND CONVERT(VARCHAR, a.MLID) = b.MLID\n" +
                "WHERE a.GSDM = ? AND a.KJND = ? AND a.MLID = ? ORDER BY a.XH", new BeanPropertyRowMapper<>(OerSecCzzfd.class), mlid.getGsdm(), mlid.getKjnd(), mlid.getMlid());
    }

    public List<OerYszb> searchYszb(OerSecCzzfMlid mlid) {
        return jdbcTemplate.query("SELECT * FROM OER_YSZB_VIEW WHERE GSDM = ? AND KJND = ? AND MLID = ?", new BeanPropertyRowMapper<>(OerYszb.class), mlid.getGsdm(), mlid.getKjnd(), mlid.getMlid());
    }
    // 查询支出功能代码 + 支出功能名称
    public OerYszb searchYszbZcgnfl(String gsdm, String kjnd, Integer mlid, Integer zbid) {
        String sql = "SELECT TOP 1 ZBGNKMDM, ZBGNKMMC FROM OER_YSZB_VIEW WHERE GSDM = ? AND KJND = ? AND MLID = ? AND ZBID = ?";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(OerYszb.class), gsdm, kjnd, mlid, zbid);
    }

    /**
     * 保存支付单
     */
    @Transactional(rollbackFor = Exception.class)
    public void saveZfd(List<OerSecCzzfd> saveList, List<OerSecCzzfMlid> mlids) {
        String insertSql = "INSERT INTO OER_SEC_CZZFD (GSDM, KJND, MLID, CRERDM, CRERMC, DJLXID, DJBH, DJLXMC, JE, ZY, JSFSMC, JSJE, JSBZ, SKR, KHYH, YHZH, GWKH, NRZY, ZFJE, ZBID, DJZBZY, ZBZY, XMDM, XMMC, YSXMDM, YSXMMC, " +
                "ZJXZDM, ZJXZMC, WHDM, WHMC, ZBZYJE, SFZFCG, ZFCGFS, ZFCGHTBH, ZFDJLX, ZFZBZY, ZFBMJJFL, ZFZFJJFL, ZFZCGNFL, ZFZJXZ, ZFZBLX, ZFXM, ZFZBWH, ZFSFBHZFCG, ZFZT, ZFZTBZXX, ZJWLDX, CRERDATE, GRDM, AUDITOR, PURPOSE) " +
                "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        for (OerSecCzzfd c : saveList) {

            // 插入支付单
            jdbcTemplate.update(
                    insertSql,
                    c.getGsdm(), c.getKjnd(), c.getMlid(), c.getCrerdm(), c.getCrermc(), c.getDjlxid(), c.getDjbh(), c.getDjlxmc(), c.getJe(), c.getZy(), c.getJsfsmc(), c.getJsje(), c.getJsbz(), c.getSkr(), c.getKhyh(), c.getYhzh(),
                    c.getGwkh(), c.getNrzy(), c.getZfje(), c.getZbid(), c.getDjzbzy(), c.getZbzy(), c.getXmdm(), c.getXmmc(), c.getYsxmdm(), c.getYsxmmc(),
                    c.getZjxzdm(), c.getZjxzmc(), c.getWhdm(), c.getWhmc(), c.getZbzyje(), c.getSfzfcg(), c.getZfcgfs(), c.getZfcghtbh(), c.getZfdjlx(),
                    null, null, null, c.getZfzcgnfl(), null, null, null, null, null, c.getZfzt(), c.getZfztbzxx(), c.getZjwldx(), c.getCrerdate(), c.getGrdm(), c.getAuditor(), c.getPurpose()
            );
        }

        // 更新 OER_SEC_CZZF_MLID 表状态
        String updateSql = "UPDATE OER_SEC_CZZF_MLID SET STATE = 1 WHERE ID = ?";
        for (OerSecCzzfMlid mlid : mlids) {
            jdbcTemplate.update(updateSql, mlid.getId());
        }
    }

    /**
     * 财政支付指标回写OerSecCzzfd
     */
    public void updateCzPayInfo(String zfid, String zfzbzy, String zfbmjjfl, String zfzfjjfl, String zfzjxz, String zfzblx, String zfxm, String zfzbwh, String zfsfbhzfcg) {
        String updateSql = "UPDATE OER_SEC_CZZFD SET ZFZBZY = ?, ZFBMJJFL = ?, ZFZFJJFL = ?, ZFZJXZ = ?, ZFZBLX = ?, ZFXM = ?, ZFZBWH = ?, ZFSFBHZFCG = ? WHERE ID = ?";
        jdbcTemplate.update(updateSql, zfzbzy, zfbmjjfl, zfzfjjfl, zfzjxz, zfzblx, zfxm, zfzbwh, zfsfbhzfcg, zfid);
    }


    public List<OerSecCzzfd> searchPayInfos() {
        return jdbcTemplate.query("SELECT * FROM OER_SEC_CZZFD WHERE ZFZT = 0", new BeanPropertyRowMapper<>(OerSecCzzfd.class));
    }

    public OerSecCzzfd searchPayInfoById(String zfid) {
        return jdbcTemplate.queryForObject("SELECT * FROM OER_SEC_CZZFD WHERE id = ? ", new BeanPropertyRowMapper<>(OerSecCzzfd.class), zfid);
    }

    public void fillFail(String zfid, String errorMessage) {
        jdbcTemplate.update("update OER_SEC_CZZFD set ZFZT = '2', ZFZTBZXX = ?, ZDSJ = CONVERT(varchar, GETDATE(), 120) where id = ?", errorMessage, zfid);
    }

    public void successNote(RequestEntity entity) {
        jdbcTemplate.update("update OER_SEC_CZZFD set ZFZT = '1', ZFZTBZXX = ?, ZDSJ = CONVERT(varchar, GETDATE(), 120) where id = ?", entity.getBillNumber(), entity.getZfid());
    }

    /**
     * 获取看板支付汇总数据
     * @return
     */
    public JSONObject getPaymentSummary() {
        JSONObject result = new JSONObject();

        // 当年总支付笔数和金额
        String yearSql = "SELECT COUNT(*) AS count, SUM(ZFJE) AS amount FROM OER_SEC_CZZFD WHERE ZDSJ >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)";
        Map<String, Object> yearData = jdbcTemplate.queryForMap(yearSql);
        result.set("yearCount", yearData.get("count") != null ? yearData.get("count") : 0);
        result.set("yearAmount", yearData.get("amount") != null ? yearData.get("amount") : 0);

        // 当月总支付笔数和金额
        String monthSql = "SELECT COUNT(*) AS count, SUM(ZFJE) AS amount FROM OER_SEC_CZZFD WHERE ZDSJ >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)";
        Map<String, Object> monthData = jdbcTemplate.queryForMap(monthSql);
        result.set("monthCount", monthData.get("count") != null ? monthData.get("count") : 0);
        result.set("monthAmount", monthData.get("amount") != null ? monthData.get("amount") : 0);

        // 待支付笔数和金额
        String pendingSql = "SELECT COUNT(*) AS count, SUM(ZFJE) AS amount FROM OER_SEC_CZZFD WHERE ZFZT = 0";
        Map<String, Object> pendingData = jdbcTemplate.queryForMap(pendingSql);
        result.set("pendingCount", pendingData.get("count") != null ? pendingData.get("count") : 0);
        result.set("pendingAmount", pendingData.get("amount") != null ? pendingData.get("amount") : 0);

        // 当月制单成功笔数和金额
        String successSql = "SELECT COUNT(*) AS count, SUM(ZFJE) AS amount FROM OER_SEC_CZZFD WHERE ZFZT = 1 AND ZDSJ >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)";
        Map<String, Object> successData = jdbcTemplate.queryForMap(successSql);
        result.set("successCount", successData.get("count") != null ? successData.get("count") : 0);
        result.set("successAmount", successData.get("amount") != null ? successData.get("amount") : 0);

        // 当月制单失败笔数和金额
        String failSql = "SELECT COUNT(*) AS count, SUM(ZFJE) AS amount FROM OER_SEC_CZZFD WHERE ZFZT = 2 AND ZDSJ >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)";
        Map<String, Object> failData = jdbcTemplate.queryForMap(failSql);
        result.set("failCount", failData.get("count") != null ? failData.get("count") : 0);
        result.set("failAmount", failData.get("amount") != null ? failData.get("amount") : 0);

        // 当月制单存疑笔数和金额
        String doubtSql = "SELECT COUNT(*) AS count, SUM(ZFJE) AS amount FROM OER_SEC_CZZFD WHERE DOUBT = 1 AND ZDSJ >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)";
        Map<String, Object> doubtData = jdbcTemplate.queryForMap(doubtSql);
        result.set("doubtCount", doubtData.get("count") != null ? doubtData.get("count") : 0);
        result.set("doubtAmount", doubtData.get("amount") != null ? doubtData.get("amount") : 0);

        return result;
    }
    /**
     * 获取看板当月各类支付单据明细统计
     * @return
     */
    public JSONArray getMonthlyPaymentDetails() {
        JSONArray result = new JSONArray();

        String sql = "SELECT ZFDJLX, COUNT ( * ) AS COUNT, SUM ( ZFJE ) AS amount \n" +
                "FROM OER_SEC_CZZFD \n" +
                "WHERE ZFZT = 1 AND ZDSJ >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) \n" +
                "GROUP BY ZFDJLX";

        List<Map<String, Object>> data = jdbcTemplate.queryForList(sql);

        for (Map<String, Object> item : data) {
            JSONObject obj = new JSONObject();
            obj.set("type", item.get("ZFDJLX") != null ? item.get("ZFDJLX") : "");
            obj.set("count", item.get("count") != null ? item.get("count") : 0);
            obj.set("amount", item.get("amount") != null ? item.get("amount") : 0);
            result.add(obj);
        }

        return result;
    }
    /**
     * 获取看板30天支付趋势数据
     * @return
     */
    public JSONArray getPaymentTrendData() {
        JSONArray result = new JSONArray();

        String sql = "SELECT CONVERT( VARCHAR ( 10 ), CONVERT ( DATE, ZDSJ ), 120 ) AS DATE, ZFDJLX, COUNT ( * ) AS COUNT, SUM ( ZFJE ) AS amount \n" +
                "FROM OER_SEC_CZZFD \n" +
                "WHERE ZFZT = 1 AND CONVERT ( DATE, ZDSJ ) >= DATEADD( DAY, - 30, GETDATE( ) ) \n" +
                "GROUP BY CONVERT ( VARCHAR ( 10 ), CONVERT ( DATE, ZDSJ ), 120 ), ZFDJLX \n" +
                "ORDER BY DATE";

        List<Map<String, Object>> data = jdbcTemplate.queryForList(sql);

        // 按日期和类型组织数据
        JSONObject dateTypeData = new JSONObject();
        for (Map<String, Object> item : data) {
            String date = (String) item.get("date");
            String type = (String) item.get("ZFDJLX");
            int count = item.get("count") != null ? ((Number) item.get("count")).intValue() : 0;
            double amount = item.get("amount") != null ? ((Number) item.get("amount")).doubleValue() : 0.0;

            if (!dateTypeData.containsKey(date)) {
                dateTypeData.set(date, new JSONObject());
            }

            JSONObject typeData = dateTypeData.getJSONObject(date);
            typeData.set(type + "_count", count);
            typeData.set(type + "_amount", amount);
        }

        // 转换为前端需要的格式
        JSONObject obj = new JSONObject();
        obj.set("dates", new ArrayList<>(dateTypeData.keySet()));
        obj.set("data", dateTypeData);
        result.add(obj);

        return result;
    }

}